<?php
class ModelCatalogAttribute extends Model {
    public function addAttribute($data) {
        $is_color_group = isset($data['is_color_group']) ? 1 : 0;
        $this->db->query("INSERT INTO " . DB_PREFIX . "attribute SET name = '" . $this->db->escape($data['name']) . "', description = '" . $this->db->escape($data['description']) . "', sort_order = '" . $this->db->escape($data['sort_order']) . "', date_added = NOW(), date_modified = NOW()" . ", status = '" . (int)$data['status'] . "', is_color_group = '" . $is_color_group . "'");
        $attribute_id = $this->db->getLastId();
        if(isset($data['attribute_values'])){
            $this->addAttributeValues($attribute_id, $data['attribute_values']);
        }
        
        $this->cache->delete('attribute');
    }
    
    public function editAttribute($attribute_id, $data) {
        $is_color_group = isset($data['is_color_group']) ? 1 : 0; 
        $current = $this->getAttribute($attribute_id);
        
        $this->db->query("UPDATE " . DB_PREFIX . "attribute SET name = '" . $this->db->escape($data['name']) . "', description = '" . $this->db->escape($data['description']) . "', sort_order = '" . $this->db->escape($data['sort_order']) . "', date_modified = NOW()" . ", status = '" . (int)$data['status'] . "', is_color_group = '" . $is_color_group . "' WHERE attribute_id = '" . (int)$attribute_id . "'");
        if($this->db->escape($data['name']) != $current['name']){
            $option_data = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option WHERE attribute_id = '" . (int)$attribute_id . "'");
            $product_option = $option_data->rows;
            foreach($product_option as $option){
                $this->db->query("UPDATE " . DB_PREFIX . "product_option_description SET name = '" . $this->db->escape($data['name']) . "' WHERE product_option_id = '" . (int)$option['product_option_id'] . "'");    
            }
        }
        if(isset($data['attribute_values'])){
            $this->editAttributeValues($attribute_id, $data['attribute_values'], $is_color_group); 
        }
        
        $this->cache->delete('attribute');
    }
    
    public function deleteAttribute($attribute_id) {
        $this->deleteAttributeValues($attribute_id);
        $this->db->query("DELETE FROM " . DB_PREFIX . "attribute WHERE attribute_id = '" . (int)$attribute_id . "'");
        
        $this->cache->delete('attribute');        
    }

    public function getAttribute($attribute_id) {
        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "attribute WHERE attribute_id = '" . (int)$attribute_id . "'");
    
        $data = $query->row;
        //$data['attribute_values'] = $this->getAttributeValues($attribute_id);
        
        return $data;
    }
    
    public function getColorAttributes() {
        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "attribute WHERE is_color_group = '1'");    
        $data = $query->rows;        
        
        return $data;
    }
    
    public function getNormalAttributes() {
        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "attribute WHERE IFNULL(is_color_group, 0) = 0");
        $data = $query->rows;        
        
        return $data;
    }
    
    public function getProductAttributes($product_ids) {
        $query = $this->db->query("SELECT DISTINCT a.* FROM " . DB_PREFIX . "attribute a LEFT JOIN product_option_value po ON (a.attribute_id = po.attribute_id) WHERE po.product_id IN (" . implode(',', $product_ids) .") AND IFNULL(is_color_group, 0) = 0");    
        $data = $query->rows;        
        
        return $data;
    }
    
    public function getSelectedAttributeValues($attribute_id, $attribute_value_ids) {
        $sql = "SELECT * FROM " . DB_PREFIX . "attribute_value WHERE attribute_id = '" . (int)$attribute_id . "' AND value_id IN(" . implode(',', $attribute_value_ids) . ") ORDER BY sort_order";
        $query = $this->db->query($sql);      
         
        return $query->rows;
    }
    
    public function getSelectedAttributeValues1($attribute_id, $product_ids) {
        $sql = "SELECT DISTINCT a.* FROM " . DB_PREFIX . "attribute_value a LEFT JOIN product_option_value po ON (a.value_id = po.attribute_value_id) WHERE a.attribute_id = '" . (int)$attribute_id . "'";
        if(!empty($product_ids)){
            $sql .= " AND po.product_id IN (" . implode(',', $product_ids) .")";
        }
        $sql .= " ORDER BY a.sort_order";
        
        $query = $this->db->query($sql);    
        
        return $query->rows;
    }
    
    public function getAttributeValues($attribute_id, $order = '') {
        if($order == ''){
            $order = ' ORDER BY sort_order ASC';
        }
        else{
            $order = ' ORDER BY ' . $order . ' ASC';    
        }
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "attribute_value WHERE attribute_id = '" . (int)$attribute_id . "' " . $order);
    
        return $query->rows;
    }
    
    public function getProductAttributeValues($attribute_id, $product_ids, $order = '') { 
        //$sql =  "SELECT av.* FROM " . DB_PREFIX . "product_option_value po RIGHT JOIN attribute_value av ON po.attribute_value_id = av.value_id WHERE po.attribute_value_id > 0 AND po.attribute_id = '" . (int)$attribute_id ."' AND av.value_id > 0 GROUP BY po.attribute_value_id";
        $sql =  "SELECT DISTINCT av.* FROM " . DB_PREFIX . "product_option_value po RIGHT JOIN attribute_value av ON po.attribute_value_id = av.value_id WHERE po.attribute_value_id > 0 AND po.attribute_id = '" . (int)$attribute_id ."'"; 
        
        if(!empty($product_ids)){
            $sql .= " AND po.product_id IN (" . implode(',', $product_ids) .")";
        }
        
        $sql .= " GROUP BY po.attribute_value_id";             
        if($order == ''){
            $sql .= ' ORDER BY sort_order ASC';
        }
        else{
            $sql .= ' ORDER BY ' . $order . ' ASC';    
        }
         
        $query = $this->db->query($sql);    
        $data = $query->rows;        
        
        return $data;
    }
    
    public function addAttributeValues($attribute_id, $data){
        if(isset($data)){            
            foreach($data as $item) {               
                $this->db->query("INSERT INTO " . DB_PREFIX . "attribute_value SET attribute_id = '" . (int)$attribute_id . "', name = '" . $this->db->escape($item['name']) . "', sort_order = '" . (int)$item['sort_order'] . "', image = '" . $this->db->escape($item['image']) . "'");
            }                    
        }
    }  
    
    public function editAttributeValues($attribute_id, $data, $is_color_group){  
        if(isset($data)){
            $this->db->query("DELETE FROM " . DB_PREFIX . "attribute_value WHERE attribute_id = '" . (int)$attribute_id . "'");
            usort($data, 'compare_id');  
            foreach ($data as $item) {  
                $color = $is_color_group ? "', image = '" . $item['image'] : '';
                $value_id = ($item['value_id'] > 0) ? "', value_id = '" . (int)$item['value_id'] : '';
                $query = "INSERT INTO " . DB_PREFIX . "attribute_value SET attribute_id = '" . (int)$attribute_id . "', name = '" . $this->db->escape($item['name']) . "', sort_order = '" . (int)$item['sort_order'] . $color . $value_id . "'"; 
                $this->db->query($query);   
                                
                $option_data = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value WHERE attribute_value_id = '" . (int)$item['value_id'] . "'");
                $product_option = $option_data->rows;  
                foreach($product_option as $option){
                    $this->db->query("UPDATE " . DB_PREFIX . "product_option_value_description SET name = '" . $this->db->escape($item['name']) . "' WHERE product_option_value_id = '" . (int)$option['product_option_value_id'] . "'");    
                }  
            }                    
        }    
    }
    
    public function deleteAttributeValues($attribute_id){
        $this->db->query("DELETE FROM " . DB_PREFIX . "attribute_value WHERE attribute_id = '" . (int)$attribute_id . "'");   
    }
    
    public function canDelete(){
        return false;    
    }
    
    public function getAttributes($data = array()) {
        if ($data) {
            $sql = "SELECT * FROM " . DB_PREFIX . "attribute";

            $sort_data = array(
                'name',
                'sort_order',
                'date_modified'
            );    
            
            if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
                $sql .= " ORDER BY " . $data['sort'];    
            } else {
                $sql .= " ORDER BY name";    
            }
            
            if (isset($data['order']) && ($data['order'] == 'DESC')) {
                $sql .= " DESC";
            } else {
                $sql .= " ASC";
            }
            
            if (isset($data['start']) || isset($data['limit'])) {
                if ($data['start'] < 0) {
                    $data['start'] = 0;
                }                

                if ($data['limit'] < 1) {
                    $data['limit'] = 20;
                }    
            
                $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
            }
            
            $query = $this->db->query($sql);             
                
            return $query->rows;
        } 
        else {
            $attribute_data = $this->cache->get('attribute');

            if (!$attribute_data) {
                $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "attribute ORDER BY sort_order ASC");                   
                $rows = $query->rows;
                /*
                foreach ($rows as $result) {
                      $attribute_data[] = array(
                        'attribute_id'  => $result['attribute_id'],
                        'name'          => $result['name'],
                        'date_modified' => $result['date_modified'],
                        'sort_order'    => $result['sort_order'],                          
                        'status'        => $result['status'],                                            
                      );
                }    
            
                $this->cache->set('attribute', $attribute_data);
                */
            }
            
            //return $attribute_data;                
            return $query->rows;    
        }
    }    
    
    public function getTotalAttributes() {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "attribute");
        
        return $query->row['total'];
    }
    
    public function getAttributesByCategoryId($category_id, $order = 'ASC', $limit = 20, $page = 0) {
        $sql = "SELECT DISTINCT po.attribute_id, pov.attribute_value_id /*, povd.name*/ FROM " . DB_PREFIX . "product p " . 
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " .
            //" LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) " . 
            " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) " . 
            " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_option po ON (p.product_id = po.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (p.product_id = pov.product_id AND po.product_option_id = pov.product_option_id) " .
            //" LEFT JOIN " . DB_PREFIX . "product_option_value_description povd ON (p.product_id = povd.product_id AND pov.product_option_value_id = povd.product_option_value_id) " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'" . 
            " AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'" . 
            " AND p2c.category_id = '" . (int)$category_id . "' AND IFNULL(po.attribute_id, 0) <> 0 ";  
            //" AND povd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
        
        //$sql .= " ORDER BY LCASE(povd.name) ASC"; // . (($order == 'DESC') ? " DESC" : " ASC");
       
        if((int)$limit > 0){
             $start = ($page - 1) * $limit;
             $start = ($start < 0) ? 0 : $start;
            
            $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
        }
        
        $query = $this->db->query($sql);
        
        return $this->createAttributesList($query->rows);           
    }
    
    public function getAttributesByKeyword($keyword, $order = 'ASC', $limit = 20, $page = 0) {
        $sql = "SELECT DISTINCT po.attribute_id, pov.attribute_value_id /*, povd.name*/ FROM " . DB_PREFIX . "product p " .  
            " LEFT JOIN " . DB_PREFIX . "product_description pd  ON (p.product_id = pd.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "stock_status ss  ON (p.stock_status_id = ss.stock_status_id) " . 
            " LEFT JOIN " . DB_PREFIX . "product_option po ON (p.product_id = po.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (po.product_option_id = pov.product_option_id) " .
            //" LEFT JOIN " . DB_PREFIX . "product_option_value_description povd ON (p.product_id = povd.product_id AND pov.product_option_value_id = povd.product_option_value_id) " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'" .
            " AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND IFNULL(po.attribute_id, 0) <> 0 " .
            //" AND povd.language_id = '" . (int)$this->config->get('config_language_id') . "'" .       
            " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
            "   OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
            "   OR LCASE(pd.meta_keywords) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
            ")";
        
        //$sql .= " ORDER BY LCASE(povd.name) ASC"; // . (($order == 'DESC') ? " DESC" : " ASC");
               
        if((int)$limit > 0){
             $start = ($page - 1) * $limit;
             $start = ($start < 0) ? 0 : $start;
            
            $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
        }
        
        $query = $this->db->query($sql);
        
        return $this->createAttributesList($query->rows);           
    }
    
    private function createAttributesList($products_data){
        $attr_query = array(); 
        foreach($products_data as $product_item){
            $attr_query[] = $product_item['attribute_id'];    
        } 
        $attr_query = array_unique($attr_query);
        
        if(empty($attr_query)){
            return array();
        }
        
        $sql = "SELECT attribute_id, `name`, sort_order FROM " . DB_PREFIX . "attribute " .
            " WHERE attribute_id IN (" . implode(',', $attr_query) .") AND IFNULL(is_color_group, 0) = 0 " .
            " ORDER BY sort_order ASC";   
                    
        $query = $this->db->query($sql);
        $attributes_data = $query->rows;            
        
        $attributes_type = array();                     
        $results = array();
        foreach($attributes_data as $attr){
            $attributes_type[] = $attr['name'];
             
            $attribute_values = array();
            foreach($products_data as $products_data_item){
                if($products_data_item['attribute_id'] == $attr['attribute_id']){
                    $attribute_values[] = $products_data_item['attribute_value_id'];
                }
            } 
                        
            $sql = "SELECT attribute_id, value_id, `name`, sort_order FROM " . DB_PREFIX . "attribute_value " .
                " WHERE attribute_id = '" . (int)$attr['attribute_id'] . "' AND value_id IN (" . implode(',', $attribute_values) .")" .
                " ORDER BY value_id, LCASE(`name`) ASC";   
                 
            $query = $this->db->query($sql);
            $attribute_values_data = $query->rows; 
            
            $av = array();
            foreach($attribute_values_data as $av_item){
                $av[] = array(
                    'attribute_id'      => $av_item['attribute_id'],
                    'value_id'          => $av_item['value_id'],
                    'name'              => $av_item['name'],
                    'sort_order'        => $av_item['sort_order'],     
                );
            }
            
            $results[] = array(
                'attribute_id'      => $attr['attribute_id'],
                'name'              => $attr['name'],
                'sort_order'        => $attr['sort_order'], 
                'attribute_values'  => $av,
            );              
        }
           
        $attributes_type = array_unique($attributes_type);     
        $result_set = array();
        foreach($attributes_type as $type_item){ 
            $result_set[$type_item] = array();         
            foreach($results as $result_item){          
                if($result_item['name'] == $type_item){
                    $result_set[$type_item][] = $result_item;
                }
            }    
        }
        
        return $result_set;
    } 

	public function getOptionsByCategory($category_id) {
		/*
        //$sql = "SELECT DISTINCT av.value_id, av.name FROM attribute a ".
		$sql = "SELECT DISTINCT po.attribute_id, pov.attribute_value_id FROM " . DB_PREFIX . "attribute a ".		
			" LEFT JOIN " . DB_PREFIX . "attribute_value av ON a.attribute_id = av.attribute_id ".
			" LEFT JOIN " . DB_PREFIX . "product_option po ON (a.attribute_id = po.attribute_id) " .
			" JOIN " . DB_PREFIX . "product_option_value pov ON av.value_id = pov.attribute_value_id ".
			" JOIN product_to_category p2c ON po.product_id = p2c.product_id ".
			" WHERE a.`name` = 'Størrelse' AND p2c.category_id =" . (int)$category_id;
			//" ORDER BY pov.sort_order, av.sort_order";                      
        */
		
		$sql = "SELECT mg.attribute_value_id, av.name, mg.attribute_id ".
			" FROM ( ".
			"	SELECT DISTINCT pov.attribute_value_id, a.attribute_id ".
			"	FROM product_option po ".
			"	JOIN product_option_value pov ON po.product_option_id = pov.product_option_id ".
			"	JOIN product_to_category p2c ON po.product_id = p2c.product_id ".
			"	JOIN attribute a ON a.attribute_id = po.attribute_id ".
			"	WHERE a.`name` = 'Størrelse' AND p2c.category_id =" . (int)$category_id .
			" ) AS mg ".
			" JOIN attribute_value av ON mg.attribute_value_id = av.value_id ".
			" ORDER BY av.attribute_id, av.sort_order, LCASE(av.`name`), av.value_id ";
		
        $query = $this->db->query($sql);
        
        return $query->rows;           
		//return current($this->createAttributesList($query->rows)); 
    }
}

function compare_id($a, $b){
    if(($a['value_id'] == 0) || ($b['value_id'] == 0)){
        return 1;    
    }
    
    // $a == $b    
    if($a['value_id'] == $b['value_id']){
        return 0;    
    }
    
    // $a < $b      
    if(($a['value_id'] < $b['value_id'])){
        return -1;  
    }   
    else{
        return 1;
    }
}
?>